﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Big_Data.Data_Base_Excel.Offres_Facteurs_Clés;
using Big_Data.Excel;
using System.Configuration;
using System.Windows.Forms.DataVisualization.Charting;
using Big_Data.Data_Base_Excel.Sante;

namespace Big_Data
{
    public partial class Form2 : Form
    {
        public string pathExcel { get; set; }
        Graphic graph;
        public static int cmpt = 0;
        public const char separator = '§';

        Dictionary<Microsoft.Office.Interop.Excel.XlChartType, string> dictchartTypeExcel = new Dictionary<Microsoft.Office.Interop.Excel.XlChartType, string>()
        {
            {Microsoft.Office.Interop.Excel.XlChartType.xlLine, "Line"},
            {Microsoft.Office.Interop.Excel.XlChartType.xlXYScatter, "Points"},
            {Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn, "Colonne"}
        };

        Dictionary<SeriesChartType, string> dictchartTypeForm = new Dictionary<SeriesChartType, string>()
        {
            {SeriesChartType.Line, "Line"},
            {SeriesChartType.Point, "Points"},
            {SeriesChartType.Column, "Colonne"}
        };

 
        public Form2()
        {
            InitializeComponent();
        }
 
        private void Form2_Load(object sender, EventArgs e)
        {
            try
            {
                FillDate();
                FillPays();
                FillTypeGraphic();                
                this.comboBoxChoix.SelectedIndex = 0;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void FillTypeGraphic()
        {
            List<string> chartType = new List<string>();
            chartType.Add("Line");
            chartType.Add("Points");
            chartType.Add("Colonne");
            comboBoxTypeGraph.DataSource = new BindingSource(chartType, null);
            comboBoxTypeGraph.DisplayMember = "Value";
        }
        public void FillDate()
        {
            Requete requete = new Requete();
            var date = requete.getDate();
            foreach (var item in date)
            {
                this.comboBoxDateDebut.Items.Add(item);
                this.comboBoxDateDebut.Text = item.ToString() ;
                this.comboBoxDateFin.Items.Add(item);
                this.comboBoxDateFin.Text = item.ToString();
            }
            this.comboBoxDateDebut.SelectedIndex = 0;
            this.comboBoxDateFin.SelectedIndex = comboBoxDateFin.MaxDropDownItems;
        }
        public void FillPays()
        {
            Requete requete = new Requete();
            var pays = requete.getPays();
            foreach (var item in pays)
	        {
		        checkedListBox2.Items.Add(item);
	        }
        }

        private void buttonPrecedent_Click(object sender, EventArgs e)
        {
            try
            {
                Form1 form1 = new Form1();
                form1.Show();
                this.Hide();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void buttonVisualiser_Click(object sender, EventArgs e)
        {
            if (checkedListBox2.CheckedItems.Count == 0)
            {
                MessageBox.Show("Selectionnner un ou plusieurs pays");
                return;
            }
            try
            {
                this.chart1.Series.Clear();
                this.chart1.Titles.Clear();

                string title = this.comboBoxChoix.SelectedItem.ToString();
                this.chart1.Titles.Add(title);

                var typeChart = (from dict in dictchartTypeForm
                                 where dict.Value.Equals(comboBoxTypeGraph.Text)
                                 select dict.Key).FirstOrDefault();

                Traitement(true, false, typeChart,title);
                this.buttonSaveGraphExcel.Enabled = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            #region visualiser
            //Requete requete = new Requete();
            //requete.datedebut = this.comboBoxDateDebut.Text;
            //requete.datefin = this.comboBoxDateFin.Text;
            //List<Requete> req = new List<Requete>();

            //try
            //{
            //    object typeChartForm = null;
            //    int index = this.comboBoxChoix.SelectedIndex;

            //    this.chart1.Series.Clear();

            //    typeChartForm = (from dict in dictchartTypeForm
            //                     where dict.Value.Equals(comboBoxTypeGraph.Text)
            //                     select dict.Key).FirstOrDefault();

            //    string title = this.comboBoxChoix.SelectedItem.ToString();

            //    foreach (var itemPays in checkedListBox2.CheckedItems)
            //    {
            //        Dictionary<string, string> dictAnnéeValeurs = new Dictionary<string, string>();
            //        List<string> lstPays = new List<string>();

            //        requete.choix_pays = itemPays.ToString();

            //        if (index == 0)
            //            req = requete.SelectEmploi();
            //        if (index == 1)
            //            req = requete.SelectPopActive();

            //        foreach (var query in req)
            //        {
            //            //Console.WriteLine(item.PAYS + " " + item.DATES + " " + item.VALEURS);
            //            dictAnnéeValeurs.Add(query.DATES, query.VALEURS);
            //            lstPays.Add(query.PAYS);
            //        }

            //        string pays = lstPays.Distinct().FirstOrDefault();
            //        GraphForm(pays, dictAnnéeValeurs, typeChartForm);
            //    }
            //}
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.Message);
            //}
            #endregion
        }

        public void SheetGraphExcel(string title)
        {
            string sheet = "Graph_" + title;
            var sheetExist = graph.ExcelScanNameSheet().Where(w => w.Equals(sheet));
            if (sheetExist.Count() == 0 || sheetExist==null)
                graph.createSheet(sheet);
            graph.OpenSheet(sheet);
        }

        public void GraphExcel(string pays,string name,Dictionary<string,string>dictAnnéeValeurs,string title,int nb,object type)
        {
            graph.AddValueSheet(pays,name, dictAnnéeValeurs);
            cmpt++;
            if(cmpt==this.checkedListBox2.CheckedItems.Count)
                graph.createGraph(title, type);
        }

        public void GraphForm(string pays,string name, Dictionary<string, string> dictAnnéeValeurs,object type)
        {
            Series serie = serie = chart1.Series.Add(pays+"("+name+")");
            serie.ChartType = (SeriesChartType)type;
            foreach (var points in dictAnnéeValeurs)
            {
                serie.Points.AddXY(points.Key, Convert.ToDouble(points.Value));
            }
        }

        private void buttonSaveGraphExcel_Click(object sender, EventArgs e)
        {
            try
            {
                graph = new Graphic();
                graph.path = pathExcel;
                graph.ExcelOpen();

                cmpt = 0;
                string title = this.comboBoxChoix.SelectedItem.ToString();

                var typeChart = (from dict in dictchartTypeExcel
                                  where dict.Value.Equals(comboBoxTypeGraph.Text)
                                    select dict.Key).FirstOrDefault();

                SheetGraphExcel(title);
                Traitement(false, true, typeChart,title);
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                graph.CleanUp();
            }
            #region save
            //try
            //{
            //    graph = new Graphic();
            //    object typeChartExcel = null;
            //    string title = this.comboBoxChoix.SelectedItem.ToString();
            //    int index = this.comboBoxChoix.SelectedIndex;

            //    Requete requete = new Requete();
            //    requete.datedebut = this.comboBoxDateDebut.Text;
            //    requete.datefin = this.comboBoxDateFin.Text;
            //    List<Requete> req = new List<Requete>();

            //    graph.path = pathExcel;
            //    graph.ExcelOpen();
            //    typeChartExcel = (from dict in dictchartTypeExcel
            //                      where dict.Value.Equals(comboBoxTypeGraph.Text)
            //                      select dict.Key).FirstOrDefault();

            //    SheetGraphExcel(title);
            //    foreach (var itemPays in checkedListBox2.CheckedItems)
            //    {
            //        Dictionary<string, string>  dictAnnéeValeurs = new Dictionary<string, string>();
            //        List<string> lstPays = new List<string>();

            //        requete.choix_pays = itemPays.ToString();

            //        if (index == 0)
            //            req = requete.SelectEmploi();
            //        if (index == 1)
            //            req = requete.SelectPopActive();

            //        foreach (var query in req)
            //        {
            //            //Console.WriteLine(item.PAYS + " " + item.DATES + " " + item.VALEURS);
            //            dictAnnéeValeurs.Add(query.DATES, query.VALEURS);
            //            lstPays.Add(query.PAYS);
            //        }

            //        string pays = lstPays.Distinct().FirstOrDefault();
            //        GraphExcel(pays, dictAnnéeValeurs, title, checkedListBox2.CheckedIndices.Count, typeChartExcel);
            //    }
            //}
            //catch(Exception ex)
            //{
            //    MessageBox.Show(ex.Message);
            //}
            //finally
            //{
            //    graph.CleanUp();
            //}
            #endregion
        }

        private void chart1_GetToolTipText(object sender, ToolTipEventArgs e)
        {
            try
            {
                if (chart1.Series.Count > 0)
                {
                    for (int i = 0; i < chart1.Series.Count; i++)
                    {
                        chart1.Series[i].ToolTip = "Valeur: #VALY\nAnnée: #VALX";
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void Traitement(bool form,bool excel,object typeChart,string title)
        {
            Requete requete = new Requete();
            RequeteSante requeteSante = new RequeteSante();
            requeteSante.datedebut = requete.datedebut = this.comboBoxDateDebut.Text;
            requeteSante.datefin = requete.datefin = this.comboBoxDateFin.Text;
            var req = new List<dynamic>();
            var req2 = new List<dynamic>();
            int index = this.comboBoxChoix.SelectedIndex;
            string name = title;
            string name2 = string.Empty;

            foreach (var itemPays in checkedListBox2.CheckedItems)
            {
                Dictionary<string, string> dictAnnéeValeurs = new Dictionary<string, string>();
                List<string> lstPays = new List<string>();

                requeteSante.choix_pays = requete.choix_pays = itemPays.ToString();

                if (index == 0)
                    req = requete.SelectEmploi().Cast<dynamic>().ToList();
                else if (index == 1)
                    req = requete.SelectPopActive().Cast<dynamic>().ToList();
                else if (index == 2)
                {
                    req = requete.SelectEmploi().Cast<dynamic>().ToList();
                    name = "(Emploi)";
                    req2 = requeteSante.SelectDepenseSante().Cast<dynamic>().ToList();
                    name2 = "(Depense)";
                }
                
                if (req.Count !=0)
                {
                    foreach (var query in req)
                    {
                        //Console.WriteLine(item.PAYS + " " + item.DATES + " " + item.VALEURS);
                        dictAnnéeValeurs.Add(query.DATES, query.VALEURS);
                        lstPays.Add(query.PAYS);
                    }

                    string pays = lstPays.Distinct().FirstOrDefault();
                    if (form)
                    {
                        GraphForm(pays,name, dictAnnéeValeurs, typeChart);
                        
                    }
                    else if (excel)
                        GraphExcel(pays,name, dictAnnéeValeurs, title, checkedListBox2.CheckedIndices.Count, typeChart);
                }
                if (req2.Count!=0)
                {
                    dictAnnéeValeurs = new Dictionary<string, string>();
                    lstPays = new List<string>();

                    foreach (var query in req2)
                    {
                        //Console.WriteLine(item.PAYS + " " + item.DATES + " " + item.VALEURS);
                        dictAnnéeValeurs.Add(query.DATES, query.VALEURS);
                        lstPays.Add(query.PAYS);
                    }

                    string pays = lstPays.Distinct().FirstOrDefault();
                    if (form)
                    {
                        GraphForm(pays, name2, dictAnnéeValeurs, typeChart);
                        req2 = new List<dynamic>();
                    }
                    else if (excel)
                        GraphExcel(pays, name2, dictAnnéeValeurs, title, checkedListBox2.CheckedIndices.Count, typeChart);
                }
                else
                    MessageBox.Show("Aucun resultat trouver");
            }
        }
    }
}
